package com.nxcy.transfer;

import com.mongodb.client.*;
import org.bson.Document;

import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.Date;

public class TransDataFromMongoToMySQL {
    public static void main(String[] args) throws Exception {
        /***
         * 链接mongo数据库
         */
        MongoClient clinet = MongoClients.create("mongodb://g08:" + URLEncoder.encode("abc@8") + "@mongo.zhledu.cn:27017/g08");
        MongoDatabase g08 = clinet.getDatabase("g08");
        MongoCollection<Document> student = g08.getCollection("student");


        /***
         * 链接mysql数据库
         */
        //1、加载驱动（获得建筑公司）
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取链接（建设一座桥）
        Connection connection = DriverManager.getConnection("jdbc:mysql://mysql.zhledu.cn:3306/school", "school", "zhli2008");
        //构建命令（找一个司机）
        PreparedStatement ps = connection.prepareStatement("INSERT INTO `g08_student_java` VALUES (?,?,?,?,?,?,?,?,?)");
        FindIterable<Document> list = student.find();
        int num = 0;
        int patchNum = 0;
        for (Document d : list) {
            String idCard = d.getString("idCard");
            int age = new Date().getYear() - Integer.parseInt(idCard.substring(6, 10)) + 1900;
            String sex = Integer.parseInt(idCard.substring(16, 17)) % 2 == 0 ? "女" : "男";
            String birthDate = idCard.substring(6, 10) + "-" + idCard.substring(10, 12) + "-" + idCard.substring(12, 14);
            ps.setInt(1, d.getInteger("_id"));
            ps.setString(2, d.getString("sno"));
            ps.setString(3, d.getString("name"));
            ps.setInt(4, age);
            ps.setString(5, sex);
            ps.setString(6, birthDate);
            ps.setString(7, idCard);
            ps.setString(8, d.getString("phone"));
            ps.setString(9, d.getString("address"));
            ps.addBatch();
            num++;
            if (num % 100 == 0) {
                ps.executeBatch();
                ps.clearBatch();
                System.out.println("执行完成" + (++patchNum) + "批次");
            }
        }
        ps.executeBatch();
        ps.clearBatch();
        System.out.println("执行完成" + (++patchNum) + "批次");
        clinet.close();
        //关闭链接（卸磨杀驴，炸桥）
        connection.close();

    }
}
